package com.sql.test.service;

import android.content.Context;
import android.os.Handler;
import android.os.Message;


import org.json.JSONArray;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;

/**
 * Created by Lenovo on 2018/5/2.
 */

public class RemoteDatabaseService implements DatabaseService{


    //用于存储获取的数据
    ArrayList<String> responseResultArray ;
    ArrayList<String[]> responseResultArrays;

    public String getGeometryColumn() {
        return geometryColumn;
    }

    @Override
    public int getRecordsNumber(Context context, String tableName) {
        return selectRecordsNumber(tableName);
    }
    //空间字段
    String geometryColumn ;
    //当前数据记录条数
    int recordsNumber ;

   //只是为了兼容本地服务，以便切换掉用，无实际意义
    @Override
    public void loadLocalDB(Context context) {

    }

    @Override
    public void connectlocalDB(Context context, int openmode) {

    }

    public int selectRecordsNumber(String tableName){
                final  String tablename = tableName;
                //存储获取的数据
                int  number =0;
                //设置要发送的数据
                JSONObject jsonObject = new JSONObject();
                HttpURLConnection connection=null;
                try{
                    jsonObject.put("tablename",tablename);
                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/SelectRecordsNumberServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }
                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        number = responseData.getInt("recordsnumber");

                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }
                return number;
            }


    @Override
    public ArrayList<String> selectTablesName(Context context) {


                //存储获取的数据
                ArrayList<String> arrayList = new ArrayList<>();

                HttpURLConnection connection=null;
                try{

                    URL url=new URL("http://172.26.104.241:8080/SelectTableServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    //connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");


                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }

                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        String recordsString = responseData.getString("tables");
                        JSONArray recordsArray = new JSONArray(recordsString);
                        for (int i = 0; i < recordsArray.length(); i++) {
                            JSONObject record = (JSONObject) recordsArray.get(i);
                            String tablename = record.getString("name");
                            arrayList.add(tablename);
                        }


                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }

        return arrayList ;

    }

    @Override
    public ArrayList<String> selectCoulmnsName(Context context, String selectedTableName) {
        final  String tablename = selectedTableName;

                //存储获取的数据
                ArrayList<String> arrayList = new ArrayList<>();
                //设置要发送的数据
                JSONObject jsonObject = new JSONObject();
                HttpURLConnection connection=null;
                try{

                    jsonObject.put("tablename",tablename);
                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/SelectColumnServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("POST");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }

                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        //空间字段单独保存
                        geometryColumn = responseData.getString("geometrycolumn");
                        String recordsString = responseData.getString("columns");
                        JSONArray recordsArray = new JSONArray(recordsString);
                        for (int i = 0; i < recordsArray.length(); i++) {
                            JSONObject record = (JSONObject) recordsArray.get(i);
                            String colunname = record.getString("name");
                            arrayList.add(colunname);
                        }

                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }
        return arrayList ;
    }

    @Override
    public ArrayList<String> selectColumnValues(Context context, String selectedTableName,  String selectedColumnName) {

        final String tablename = selectedTableName;
        final  String columnname = selectedColumnName;

                //存储获取的数据
                ArrayList<String> arrayList = new ArrayList<>();
                //设置要发送的数据
                JSONObject jsonObject = new JSONObject();
                JSONArray columnsArray = new JSONArray();
                HttpURLConnection connection=null;
                try{

                    jsonObject.put("tablename",tablename);
                    jsonObject.put("columnname",columnname);
                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/SelectColumnValueServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("POST");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }


                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        String recordsString = responseData.getString("values");
                        JSONArray recordsArray = new JSONArray(recordsString);
                        for (int i = 0; i < recordsArray.length(); i++) {
                            JSONObject record = (JSONObject) recordsArray.get(i);

                            String value =record.getString("value");
                            arrayList.add(value);
                        }



                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }

        return arrayList ;

    }

    @Override
    public ArrayList<String[]> selectFeatureData(Context context, final String selectedTableName, final String selectedColumnName, final String selectedColumnValue, final ArrayList<String> columns, final String geometryColumn) {



                    //存储获取的数据
                    ArrayList<String[]> arrayList = new ArrayList<>();
                    //设置要发送的数据
                    JSONObject jsonObject = new JSONObject();
                    JSONArray columnsArray = new JSONArray();
                    HttpURLConnection connection=null;
                    try{

                        jsonObject.put("tablename",selectedTableName);
                        jsonObject.put("columnname",selectedColumnName);
                        jsonObject.put("columnvalue",selectedColumnValue);
                        for(int i=0;i<columns.size();i++){
                            JSONObject jsonObject1= new JSONObject();
                            jsonObject1.put("name",columns.get(i));
                            columnsArray.put(jsonObject1);
                        }
                        jsonObject.put("columns",columnsArray);
                        jsonObject.put("geom",geometryColumn);
                        String data = String.valueOf(jsonObject);

                        URL url=new URL("http://172.26.104.241:8080/SelectDataByColumnValueServlet");
                        connection=(HttpURLConnection)url.openConnection();
                        connection.setRequestMethod("POST");
                        connection.setConnectTimeout(20000);
                        connection.setReadTimeout(8000);
                        connection.setDoOutput(true);
                        connection.setDoInput(true);
                        connection.setRequestProperty("Contection","Keep-Alive");
                        connection.setRequestProperty("Charset","UTF-8");
                        connection.setRequestProperty("Content-Type","application/json");
                        connection.setRequestProperty("accept","application/json");

                        OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                        out.write(data);
                        out.flush();
                        out.close();
                        //下面对获取到的输入流进行读取
                        if(connection.getResponseCode()==200) {
                            InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                            BufferedReader bufr = new BufferedReader(in);
                            StringBuilder response = new StringBuilder();
                            String line = null;
                            while ((line = bufr.readLine()) != null) {
                                response.append(line);
                            }

                            //获取数据解析
                            JSONObject responseData = new JSONObject(response.toString());
                            String recordsString = responseData.getString("records");
                            JSONArray recordsArray = new JSONArray(recordsString);
                            for (int i = 0; i < recordsArray.length(); i++) {
                                JSONObject record = (JSONObject) recordsArray.get(i);
                                //存储所有字段取值，包括空间字段，所以+1
                                String[] recordString = new String[columnsArray.length()+1];
                                for(int j=0;j<columnsArray.length();j++) {
                                    String value = record.getString(columns.get(j));
                                    recordString[j] = value;
                                }
                                //获取空间字段取值
                                recordString[columnsArray.length()]=record.getString(geometryColumn);
                                arrayList.add(recordString);
                            }

                        }

                    }catch(Exception e){
                        e.printStackTrace();
                    }finally {
                        if(connection!=null){
                            connection.disconnect();
                        }
                    }
           return arrayList ;
    }

    @Override
    public ArrayList<String> selectTrigger(Context context, String tableName) {
        return null;
    }

    @Override
    public boolean dropAllTrigger(Context context, String tableName) {
        return false;
    }

    @Override
    public boolean insertFeatureData(Context context, String insertTableName, ArrayList<String> insertTableColumns, ArrayList<String> insertFeatureData, String geometryColumn) {

        final  String tablename = insertTableName;
        final String geom = geometryColumn;
        final ArrayList<String> columns = insertTableColumns;
        final  ArrayList<String> featuredata = insertFeatureData;


                //设置要发送的数据
                JSONObject jsonObject = new JSONObject();
                JSONArray columnsArray = new JSONArray();
                JSONArray valueArray = new JSONArray();
                HttpURLConnection connection=null;
                try{

                    jsonObject.put("tablename",tablename);
                    jsonObject.put("geom",geom);
                    for(int i=0;i<columns.size();i++){
                        JSONObject jsonObject1= new JSONObject();
                        jsonObject1.put("name",columns.get(i));
                        columnsArray.put(jsonObject1);
                    }
                    for(int i=0;i<featuredata.size();i++){
                        JSONObject jsonObject2= new JSONObject();
                        jsonObject2.put("value",featuredata.get(i));
                        valueArray.put(jsonObject2);
                    }
                    jsonObject.put("columns",columnsArray);
                    jsonObject.put("values",valueArray);

                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/InsertDataServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("POST");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }


                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        String resultString = responseData.getString("result");
                        if(resultString.toLowerCase().equals("true")){
                            return true;
                        }else{
                            return false;
                        }
                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }
               return false;
    }

    @Override
    public boolean createTable(Context context, String tableName, String geometryType, ArrayList<String[]> columnsInfo) {

      final  String tablename = tableName;
      final String geom = geometryType;
      final ArrayList<String[]> columns = columnsInfo;

                //存储获取的数据
                ArrayList<String[]> arrayList = new ArrayList<>();
                //设置要发送的数据
                JSONObject jsonObject = new JSONObject();
                JSONArray columnsArray = new JSONArray();
                HttpURLConnection connection=null;
                try{

                    jsonObject.put("tablename",tablename);
                    jsonObject.put("geom",geom);
                    for(int i=0;i<columns.size();i++){
                        JSONObject jsonObject1= new JSONObject();
                        jsonObject1.put("name",columns.get(i)[0]);
                        columnsArray.put(jsonObject1);
                    }
                    jsonObject.put("columns",columnsArray);
                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/CreateTableServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("POST");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }

                        //获取数据解析
                        JSONObject responseData = new JSONObject(response.toString());
                        String resultString = responseData.getString("result");
                        if(resultString.toLowerCase().equals("true")){
                            return true;
                        }else{
                            return false;
                        }
                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }

        return false;
    }

    public ArrayList<String[]> selectDataByKeyWord(String keywordString){

                String  keyword =keywordString;
                ArrayList<String[]> arrayList = new ArrayList<>();
                JSONObject jsonObject = new JSONObject();
                HttpURLConnection connection=null;
                try{
                    jsonObject.put("keyword",keyword);
                    String data = String.valueOf(jsonObject);

                    URL url=new URL("http://172.26.104.241:8080/SelectPoiServlet");
                    connection=(HttpURLConnection)url.openConnection();
                    connection.setRequestMethod("POST");
                    connection.setConnectTimeout(20000);
                    connection.setReadTimeout(8000);
                    connection.setDoOutput(true);
                    connection.setDoInput(true);
                    connection.setRequestProperty("Contection","Keep-Alive");
                    connection.setRequestProperty("Charset","UTF-8");
                    connection.setRequestProperty("Content-Type","application/json");
                    connection.setRequestProperty("accept","application/json");

                    OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream(),"UTF-8");
                    out.write(data);
                    out.flush();
                    out.close();
                    //下面对获取到的输入流进行读取
                    if(connection.getResponseCode()==200) {
                        InputStreamReader in = new InputStreamReader(connection.getInputStream(), "UTF-8");
                        BufferedReader bufr = new BufferedReader(in);
                        StringBuilder response = new StringBuilder();
                        String line = null;
                        while ((line = bufr.readLine()) != null) {
                            response.append(line);
                        }

                        //获取数据解析
                      JSONObject responseData = new JSONObject(response.toString());
                        String recordsString = responseData.getString("records");
                        JSONArray recordsArray = new JSONArray(recordsString);
                        for (int i = 0; i < recordsArray.length(); i++) {
                            JSONObject record = (JSONObject) recordsArray.get(i);
                            String name = record.getString("name");
                            String address = record.getString("address");
                            String geom = record.getString("geom");
                            String[]  recordString = new String[3];
                            recordString[0] = name;
                            recordString[1] =address;
                            recordString[2] = geom;
                            arrayList.add(recordString);

                        }
                    }

                }catch(Exception e){
                    e.printStackTrace();
                }finally {
                    if(connection!=null){
                        connection.disconnect();
                    }
                }
                return  arrayList;
    }

}
